Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Data Access Patterns

The data access patterns in a purely OLTP system are fairly straightforward. Based on the types of transactions you generate, you should be able to fairly accurately determine these patterns. Although each system has its own specific data access patterns, OLTP systems have the following general characteristics.

  Data access to an individual redo log file for logging is always sequential and write only.
  The LGWR writes to the redo log file in a size that may vary from the size of the redo entry up to the size of the redo log buffer.
  Data access to the individual redo log files for archiving is always sequential and read only.
  If more than one redo log file is on a disk volume and archiving is enabled (you should always archive), the access to those disks is not sequential because the disk heads must seek between the log writes and the archive reads.
  Data access to the data files is random. Data accesses to the data files may be random or sequential on a transactional basis but because many of these transactions occur simultaneously, the data files are always accessed in a random fashion. Most transactions are small.
  Most of the time, data reads and writes from the data files occur in DB_BLOCK_SIZE sizes. Because OLTP systems usually do not have very many table scans, it is unlikely that multiblock reads and writes will happen.
  The typical OLTP system is characterized by some data that is “hot” and some data that is “cold.” Hot data is accessed much more frequently than cold data. The general rule of thumb is that 80 percent of the data accesses occur in 20 percent of the data (a phenomenon sometimes known as the 80-20 rule).

These patterns vary depending on how your system operates, but the general principles are the same. You most likely store some historical data in a sequential manner. You probably store new data at the end of existing tables, thus limiting where the data is hot.

System Load

In an OLTP system, the system designers typically have a good idea what kind of load to expect on the system. These factors are used to determine the system configuration. A typical OLTP system must support a large number of users, which indicates that the system runs under a heavy load most of the time.


NOTE:  In the real world, I typically see OLTP systems that show a significant I/O deficiency. By having an I/O capacity that does not fit the system load, you frequently see significant CPU idle cycles while you wait for I/Os to complete. An idle CPU can severely degrade performance.

The following list shows some of the typical load characteristics of an OLTP system:

  Significant numbers of process switches or thread switches. Because there are many user processes, the system must continually switch between these processes.
  Heavy network traffic. Each transaction most likely generates several network packets. Depending on the use of stored procedures, network traffic can be reduced.
  Heavy I/O usage. OLTP systems usually generate large numbers of random I/Os to the data files.
  Heavy redo log usage. Because OLTP transactions consist of both read and update activity, the redo log is heavily accessed.
  Heavy use of rollback segments. Because OLTP transactions consist of significant update activity, the rollback segments are heavily used.
  Large amounts of memory. The memory is used not only for the SGA, but for each of the server processes.

You can use these characteristics to help design and tune your OLTP system for optimal performance. The first step in the design process is to set goals for what you want to achieve.

Goals

The goal in tuning the OLTP system is to achieve a system with certain characteristics. Here are the characteristics of an optimally tuned OLTP system:

  The system offers good response times. For the majority of transactions, response times are within the specified range. Response time criteria usually specify a maximum response time for 90 to 95 percent of the transactions. (It is almost impossible to guarantee a certain response time for 100 percent of the transactions.)
  The system does not show any characteristics of being drive bound. Any disk bottleneck degrades performance. If the system is disk bound, you should either add more disks or increase memory.
  Memory is sufficient. If the machine is paging or swapping, performance is being severely degraded. The best alternative is to add more memory; if that is not possible, reduce the size of the SGA or the number of users until the system no longer pages or swaps.
  There are a sufficient number of rollback segments to avoid rollback contention. The system designer can easily avoid rollback contention by knowing the number of server processes and data access patterns.
  There is sufficient space in the shared pool. With large numbers of users, the shared SQL area is very important. Careful tuning is required to ensure that the shared pool isn’t overloaded.
  The system can handle peak loads. It is not sufficient that the system can handle steady-state operations. During a checkpoint, the system must also provide adequate response times.
  The system meets any additional requirements you have. If you have requirements for continual archiving to a remote machine or a short window for backups, these factors must also be taken into consideration.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.